function f_check_mdl_and_delay()
{
    declare order_id check_ip check_port check_role delay_time is_has_mdl get_node_id mdl_sql_idcheck_opt __ get_next_time tmp_str
    tmp_str="e6f2221a8f8774921ade1f814fb1c456"
    check_ip="${1}"
    check_port="${2}"
    check_role="${3}"
    order_id="${4}"
    if [ "${check_role}x" == "masterx" ]
    then
        delay_time="0"
    else
        delay_time="$(timeout ${exe_time} ${mysql_comm} -e "show slave status\G" 2>/dev/null|grep "Seconds_Behind_Master: ")" || true
        delay_time="$(awk '{if ($NF=="NULL"){print 0}else{if($NF==0){print 0}else print $NF}}' <<< "${delay_time}")" || true
    fi
    __="select count(*) from ${isdbp} where STATE like 'Waiting for table metadata lock%';"
    is_has_mdl="$(timeout ${exe_time} ${mysql_comm} -NBe "${__}" 2>/dev/null)" && exe_state="${?}" || exe_state="${?}"
    if [ "${is_has_mdl}x" == "0x" ] || [ ${exe_state} -ne 0 ] || [ -z "${is_has_mdl}" ]
    then
        return 0
    fi
    echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ is_has_mdl : '${is_has_mdl}' ]" >> ${log_file}
    
    #获取节点id号
    get_node_id="${order_id}"
    mdl_sql_id="${get_node_id}"
    __="select UNIX_TIMESTAMP(send_time) + if(next_time=2,5,if(next_time=3,15,if(next_time=4,30,next_time))) * 60-UNIX_TIMESTAMP(now()) < 0"
    __="${__} from ${cmadi} where oid = ${get_node_id} order by id desc limit 1;"
    # 结果应该是0 1,1表示需要查找, 0表示不用查找, 针对发送邮件脚本, 即0表示不发送邮件, 发送邮件是根据这个返回状态码去判断是否发送邮件
    check_opt="$(timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null)" && exe_state="${?}" || exe_state="${?}" 
    [ -z "${check_opt}" ] && check_opt=1
    if [ "${check_opt}x" == "0x" ]
    then
        return 1
    elif [ ${exe_state} -ne 0 ]
    then
        return 1
    fi

    #获取下一次发送邮件的时间
    __="select if(next_time=4,1,next_time+1) from ${cmadi} where oid = ${get_node_id} order by id desc limit 1;"
    get_next_time="$(timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null)" && exe_state="${?}" || exe_state="${?}" 
    #上一次检查如果不是每个轮询的第一次的时间则+1, 否则是1, 如果是空也是1
    [ -z "${get_next_time}" ] && get_next_time=1
    [ "${check_role}x" == "slavex" ] && get_next_time=1 #slave角色每次检查到mdl都会释放空连接所以释放完也将轮询时间置为1

    #向表种写入mdl信息
    echo "$(
        __="select concat(time,'@@@',info,'@@@',substring_index(host,':',1)) from ${isdbp} 
            where STATE like 'Waiting for table metadata lock%' order by time desc;"
        timeout ${exe_time} ${mysql_comm} -NBe "${__}" 2>/dev/null|awk -F'@@@' -v mdl_sql_id="${mdl_sql_id}" 'BEGIN { 
            print "begin;"
        } 
        {
            insert="insert into '${cmadfmi}'(id,sql_id,sql_info, block_time, curr_time,client_ip) select 0"
            print insert","mdl_sql_id",'\''"gensub("'\''", t, "g", $2)"'\'',"$1",now(),'\''"$3"'\'';"
        } END { print "commit;" }'
        __="select concat(t2.state,'@@@',t1.trx_started,'@@@',t1.trx_query,'@@@',substring_index(t2.host,':',1)) from ${isdbit} t1 
            join ${isdbp} t2 on t1.trx_mysql_thread_id = t2.id where (t1.trx_query is not null) or (t1.trx_query != '');"
        timeout ${exe_time} ${mysql_comm} -NBe "${__}" 2>/dev/null|awk -F'@@@' -v mdl_sql_id="${mdl_sql_id}" -v t="${tmp_str}" 'BEGIN {
            print "begin;"
        }
        { 
            insert="insert into '${cmadfcsi}'(id,sql_id,exe_sql_info,start_time,exe_state,curr_time,client_ip) select 0"
            print insert","mdl_sql_id",'\''"gensub("'\''", t, "g", $3)"'\'','\''"$2"'\'','\''"$1"'\'',now(),'\''"$4"'\'';"
        } END { print "commit;" }'
        __="insert into ${cmadi}(id,oid,next_time,delay_time,mdl_id,send_time,send_state)"
        echo "${__} select 0,${get_node_id},${get_next_time},${delay_time},${mdl_sql_id},now(),1;"
    )" |sed "s/${tmp_str}/\\\'/g" | timeout 120s ${mysql_comm_dbadb} 2>/dev/null

    __="select 1;"
    if [ "${check_role}x" == "slavex" ]
    then #如果是slave角色就kill掉空连接
        __="select concat('kill ',t2.id,';') from ${isdbit} t1 join ${isdbp} t2 on t1.trx_mysql_thread_id = t2.id 
            where t2.user not in ('replica','root','binlogin','system user') and ((t2.command = 'Sleep' and t2.state = '' and t2.info is null)
            or ((info like 'select %' or info like 'select\n%') and t2.time > 60));"
    elif [ "${check_role}x" == "masterx" ]
    then #如果是mas角色就kill掉空连接
        __="select concat('kill ',t2.id,';') from ${isdbit} t1 join ${isdbp} t2 on t1.trx_mysql_thread_id = t2.id
            where t2.user not in ('replica','root','binlogin','system user') and (t2.command = 'Sleep' and t2.state = '' and t2.info is null);"
    fi
    timeout ${exe_time} ${mysql_comm} -NBe "${__}" 2>/dev/null|timeout ${exe_time} ${mysql_comm} -f 2>/dev/null
}

function f_update_send_state()
{
    declare _oid_ __
    _oid_="${1}"
    __="update ${cmadi} set send_state = 0 where oid = ${_oid_};"
    timeout ${exe_time} ${mysql_comm_dbadb} -e "${__}" 2>/dev/null && exe_state="${?}" || exe_state="${?}"
    if [ ${exe_state} -ne 0 ]
    then
        echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ 在 '${dbadb_port}:${dbadb_db}' 库更新mdl状态失败,'${__}' ]" >> ${log_file}
        return 1
    fi
}

function f_get_html_info_for_mdl()
{
    declare _oid_ _port _host _role time_now check_type send_mail_for_count __ get_send_info_pk check_type
    declare get_host_info table_host table_role table_delay_time __ table_column column curexe_has_res col_file
    declare -a table_row
    _host="${1}"
    _role="${2}"
    _oid_="${3}"
    _port="${4}"
    check_type="改表"
    col_file="${work_dir}/logs/.col${_port}.log"             #临时文件
    [ -z "${check_mysql_port}" ] && check_type="巡检" || check_type="改表"
    time_now="$(date "+%F %H:%M:%S")"
    __="select id from ${cmadi} where send_state = 1 and oid = ${_oid_};"
    table_row=($(timeout 120s ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null)) && exe_state="${?}" || exe_state="${?}"
    if [ ${exe_state} -ne 0 ]
    then
        [ -f "${run_file}" ] && rm -f "${run_file}"
        echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ 在 '${dbadb_port}:${dbadb_db}' 库查找mdl信息失败, '${__}' ]" >> ${log_file}
        return 1
    elif [ -z "${table_row}" ]
    then
        [ -f "${run_file}" ] && rm -f "${run_file}"
        echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ 在 '${dbadb_port}:${dbadb_db}' 库查找需要发送邮件的信息结果为空, '${__}' ]" >> ${log_file}
        return 1
    fi
    __="select count(*) from ${cmadi} t1 join ${cmadfmi} t2 on t1.mdl_id = t2.sql_id 
        where t1.send_state = 1 and t2.sql_info != '' and t1.oid = ${_oid_};"
    send_mail_for_count="$(timeout 120s ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null)" || true
    if [ "${send_mail_for_count}x" == "0x" ]
    then
        [ -f "${run_file}" ] && rm -f "${run_file}"
        echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ 在'${dbadb_port}:${dbadb_db}'库查找mdl信息结果为空,'${__}' ]" >> ${log_file}
        return 1
    fi
    get_send_info_pk="$(tr " " "," <<< "${table_row[@]}"|sed s/,$//g)"
    echo "
<html>
    <head>
        <meta http-equiv='Content-Type' content='text/html; charset=utf-8' />
        <title>mdl状态信息展示</title>
    </head>
    <body>
        <p>端口: ${_port}, 采集时间: ${time_now} </p>
        <table  border='1' cellspacing='0'>
            <caption>mdl状态检查</caption>
            <tr>
                <th width=100>主机</th> 
                <th width=150>延迟时间</th> 
                <th width=150>释放连接</th> 
                <th width=150>来源ip</th> 
                <td width=150>阻塞时间</td><td width=1500>受阻塞sql</td>
            </tr>
$(
    for (( row=0; row<${#table_row[@]}; row++))
    do
        __="select concat(delay_time,'s') from ${cmadi} where oid = ${_oid_}"
        get_host_info="$(timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null)" && exe_state="${?}" || exe_state="${?}"
        if [ ${exe_state} -ne 0 ]
        then
            [ -f "${run_file}" ] && rm -f "${run_file}"
            echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ 在 '${dbadb_port}:${dbadb_db}' 库查找mdl信息失败,'${__}' ]" >> ${log_file}
            return 1
        fi
        table_host="${_host}"
        table_role="$(awk '{if ($1 == "slave"){print "YES"}else {print "NO"}}' <<< "${_role}")"
        table_delay_time="${get_host_info}"
        __="select concat(t2.client_ip,'</td><td>',block_time,'s</td><td>',sql_info) from ${cmadi} t1 
            join ${cmadfmi} t2 on t1.mdl_id = t2.sql_id where t1.id = ${table_row[${row}]} and t2.sql_info != '';"
        timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" > ${col_file} 2>/dev/null && exe_state="${?}" || exe_state="${?}"
        if [ ${exe_state} -ne 0 ]
        then
            [ -f "${run_file}" ] && rm -f "${run_file}"
            __="在 '${dbadb_port}:${dbadb_db}' 库查找mdl信息失败, '${__}'"
            echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ ${__} ]" >> ${log_file} && return 1
        fi
        table_column="$(wc -l < ${col_file})"
        echo -e "
            <tr>
                <th rowspan='${table_column}'>${table_host}</th>
                <th rowspan='${table_column}'>${table_delay_time}</th>
	        <th rowspan='${table_column}'>${table_role}</th>
                <td>$(sed -n '1p' ${col_file})</td>
            </tr>\n
            $(
                for ((column=2; column<=${table_column};column++))
                do
                    echo -n "\n            <tr><td>$(sed -n "${column}p" ${col_file})</td></tr>"
                done
            )
            "
        [ -f "${col_file}" ] && rm -f ${col_file}
    done
    __="select concat(exe_state,'</td><td>',start_time,'</td><td>',exe_sql_info) from ${cmadi} t1 
        join ${cmadfcsi} t2 on t1.mdl_id = t2.sql_id where t1.id = ${table_row[0]} and t2.exe_sql_info != '';"
    curexe_has_res="$(timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null|wc -l)" && exe_state="${?}" || exe_state="${?}"
    if [ "${curexe_has_res}x" == "0x" ]
    then
        echo "
        </table>
        <p>
            <span style='color:red;'>
                <strong> 建议：${check_type}发现有空连接占用资源导致mdl等待,建议优化一下业务逻辑,将空连接及早释放,降低mdl出现的概率!</strong>
            </span>
        </p>"
    elif [ "${curexe_has_res}x" != "0x" ]
    then
        echo "
        </table>
        <p>  &nbsp; </p>
        <p>  &nbsp; </p>
        <p>  &nbsp; </p>
        <p><span style='color:red;'><strong> 建议：${check_type}发现如下sql占用资源导致mdl等待,如果是慢查询建议优化一下,降低mdl出现的概率!</strong></span></p>
        <p><span style='color:red;'><strong> 该实例当前正在执行的事务采集如下 </strong></span></p>
        <table  border='1' cellspacing='0'>
            <tr>
                <th width=100>主机</th> 
                <th width=50>来源ip</th>
                <th width=150>执行状态</th> 
                <td width=300>执行开始时间</td><td width=1500>当前正在执行的sql</td>
            </tr>"
        for (( row=0; row<${#table_row[@]}; row++))
        do
            __="select concat(delay_time,'s') from ${cmadi} where oid = ${_oid_}"
            get_host_info="$(timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" 2>/dev/null)" && exe_state="${?}" || exe_state="${?}"
            if [ ${exe_state} -ne 0 ]
            then
                [ -f "${run_file}" ] && rm -f "${run_file}"
                __="在 '${dbadb_port}:${dbadb_db}' 库查找mdl信息失败, '${__}'"
                echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ ${__} ]" >> ${log_file} && return 1
            fi
            table_host="${_host}"
            table_delay_time="${get_host_info}"
            __="select concat(t2.client_ip,'</td><td>',exe_state,'</td><td>',start_time,'</td><td>',exe_sql_info) from ${cmadi} t1 
                join ${cmadfcsi} t2 on t1.mdl_id = t2.sql_id where t1.id = ${table_row[${row}]};"
            timeout ${exe_time} ${mysql_comm_dbadb} -NBe "${__}" > ${col_file} 2>/dev/null && exe_state="${?}" || exe_state="${?}"
            if [ ${exe_state} -ne 0 ]
            then
                [ -f "${run_file}" ] && rm -f "${run_file}"
                __="在 '${dbadb_port}:${dbadb_db}' 库查找mdl信息失败, '${__}'"
                echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ ${__} ]" >> ${log_file} && return 1
            fi
            table_column="$(wc -l < ${col_file})"
            echo -e "
            <tr>
                <th rowspan='${table_column}'>${table_host}</th>
                <td>$(sed -n '1p' ${col_file})</td>
            </tr>\n
            $(
                for ((column=2; column<=${table_column};column++))
                do
                    echo -n "\n            <tr><td>$(sed -n "${column}p" ${col_file})</td></tr>"
                done
            )
            "
            [ -f "${col_file}" ] && rm -f ${col_file}
        done
    fi
)
        </table>
    </body>
</html>" && exe_state="${?}" || exe_state="${?}"
    [ -f "${run_file}" ] && rm -f "${run_file}"
    if [ ${exe_state} -ne 0 ]
    then
        echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ 生成html代码失败 ]" >> ${log_file} && return 1
    fi
}

function f_get_html_info_prepare_for_mdl()
{
    declare order_id _port _host _role send_error backup_html title_info _html_file
    _host="${1}"
    _role="${2}"
    order_id="${3}"
    _port="${4}"
    _html_file="${html_file}.mdl"
    f_get_html_info_for_mdl "${_host}" "${_role}" "${order_id}" "${_port}" > ${_html_file} || true
    if [ "$(grep -c "html" ${_html_file})x" == "0x" ]
    then
        backup_html="${_html_file}.backup_html.$(date "+%H%M%S")"
        if [ "$(grep -Pc "[a-zA-Z]" ${_html_file})x" != "0x" ]
        then
            mv ${_html_file} ${backup_html}
            echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 提示 ] [ html文件'${_html_file}'格式错误 ]" >> ${log_file}
        else
            [ -f "${_html_file}" ] && rm -f "${_html_file}"
        fi
    fi
    title_info="【巡检】${_port}-mdl巡检"
    ${send_mail_script} -s "${title_info}" -u ${g_user_mail_addr} -f ${_html_file} -t html && exe_state="${?}" || exe_state="${?}"
    if [ ${exe_state} -ne 0 ]
    then
        send_error=1
    else
        f_update_send_state "${order_id}" || true
        [ -f "${_html_file}" ] && rm -f "${_html_file}"
    fi

    if [ "${send_error}x" == "1x" ]
    then
        echo "[ ${localhost_ip} ] [ $(date "+%F %H:%M:%S") ] [ 错误 ] [ 部分邮件发送失败 ]" >> ${log_file}
        return 1
    else
        return 0
    fi
}

